22.02.21 - SQL Advanced Features

SELECT
 [DISTINCT | ALL] column-list
    FROM table-names
    [WHERE condition]
    [ORDER BY column-list]
    [GROUP BY column-list]
    [HAVING condition]

DISTINCT and ALL

• Using DISTINCT removes duplicates
• Using ALL retains duplicates
• ALL is used as a default if neither is supplied
• Can work over multiple columns

WHERE Clauses

• Restricts rows that are returned
• Takes the form of a condition - only rows that satisfy the condition are returned

SELECT

• Combine information from two or more tables
• If columns have same name, can be resolved by using TableName.ColumnName (Resolves ambiguity)

Subqueries

A SELECT statement can be nested inside another query to form a subquery. The result of the subquery are passed back to the containing query

• Often will return a set of values rather than a single value
• Cannot directly compare a single value to a set. Doing so will result in an error
• Options for handling sets:
  • IN
  • EXISTS
  • ALL/ANY
  • NOT

IN

• Using IN can see if a given value is in a set of values
• NOT IN checks to see if a given value is not in the set
• The set can be given explicitly or can be produced in a subquery

Aliases

Aliases rename columns or tables

• Can make names more meaningful
• Can shorten names, making them easier to use
• Can resolve ambiguous names

EXISTS - Can see whether there is at least one element in a given set ANY and ALL - Compare a single value to a set of values

Type	Description	Example
DATE	A Day, Mont, Year	'1981-12-16’ or ‘81-12-16'
Time	House, Minutes, Seconds	’15:24:39’
DATETIME	Combination of above	‘1981-12-16 15:24:39’